<?php

namespace Common\Service;

class ExcelService {

  private static $instance;

  public static function instance() {
    if (self::$instance == null) {
      $instance = new ExcelService();
    }
    return $instance;
  }

  public function export($filename, $title, $data, $with_title = FALSE, $with_footer = FALSE) {
    //$filename .= '_' . date('Y_m_d');
    $fileName = iconv('utf-8', 'gb2312', $filename); //文件名称

    $cellNum = count($title);
    $dataNum = count($data);

    vendor("PHPExcel.PHPExcel");

    $objPHPExcel = new \PHPExcel();
    $cellName = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ');
    $objActSheet = $objPHPExcel->getActiveSheet(0);

    $first_line = 1;
    if ($with_title) {
      //设置Title
      $objActSheet->mergeCells('A1:' . $cellName[$cellNum - 1] . '1'); //合并单元格
      $objActSheet->setCellValue('A1', $filename);
      $objActSheet->getStyle('A1')->getFont()->setName("微软雅黑");
      $objActSheet->getStyle('A1')->getFont()->setSize(20);
      $objActSheet->getStyle('A1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

      //设置时间
      $objActSheet->mergeCells('A2:' . $cellName[$cellNum - 1] . '2'); //合并单元格
      $objActSheet->setCellValue('A2', '导出时间:' . date('Y-m-d H:i:s'));
      $objActSheet->getStyle('A2')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);

      $first_line = 3;
    }

    for ($i = 0; $i < $cellNum; $i++) {
      $objActSheet->setCellValue($cellName[$i] . $first_line, $title[$i][1]);
    }

    // Miscellaneous glyphs, UTF-8   
    for ($i = 0; $i < $dataNum; $i++) {
      for ($j = 0; $j < $cellNum; $j++) {
        $objActSheet->setCellValue($cellName[$j] . ($i + $first_line + 1), $data[$i][$title[$j][0]]);
      }
    }

    if ($with_footer) {
      $last_line_num = $dataNum + $first_line + 1;

      //设置文件尾
      //设置时间
      $objActSheet->mergeCells('A' . $last_line_num . ':' . $cellName[$cellNum - 1] . $last_line_num); //合并单元格
      $objActSheet->setCellValue('A' . $last_line_num, '审核人：                  负责人：                       ');
      $objActSheet->getStyle('A' . $last_line_num)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
    }
    header('pragma:public');
    header('Content-type:application/vnd.ms-excel;charset=utf-8;name="' . $fileName . '.xls"');
    header("Content-Disposition:attachment;filename=$fileName.xls"); //attachment新窗口打印inline本窗口打印
    $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
    $objWriter->save('php://output');
    exit;
  }

  public function export_to_file($filename, $title, $data) {
    //$filename .= '_' . date('Y_m_d');
    $fileName = iconv('utf-8', 'gb2312', $filename); //文件名称

    $cellNum = count($title);
    $dataNum = count($data);

    vendor("PHPExcel.PHPExcel");

    $objPHPExcel = new \PHPExcel();
    $cellName = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ');
    $objActSheet = $objPHPExcel->getActiveSheet(0);

    //设置Title
    $objActSheet->mergeCells('A1:' . $cellName[$cellNum - 1] . '1'); //合并单元格
    $objActSheet->setCellValue('A1', $filename);
    $objActSheet->getStyle('A1')->getFont()->setName("微软雅黑");
    $objActSheet->getStyle('A1')->getFont()->setSize(20);
    $objActSheet->getStyle('A1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

    //设置时间
    $objActSheet->mergeCells('A2:' . $cellName[$cellNum - 1] . '2'); //合并单元格
    $objActSheet->setCellValue('A2', '导出时间:' . date('Y-m-d H:i:s'));
    $objActSheet->getStyle('A2')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);


    for ($i = 0; $i < $cellNum; $i++) {
      $objActSheet->setCellValue($cellName[$i] . 3, $title[$i][1]);
    }

    // Miscellaneous glyphs, UTF-8   
    for ($i = 0; $i < $dataNum; $i++) {
      for ($j = 0; $j < $cellNum; $j++) {
        $objActSheet->setCellValue($cellName[$j] . ($i + 4), $data[$i][$title[$j][0]]);
      }
    }

    $save_name = 'Public/tmp/excel/' . $filename . '.xls';
    if (file_exists($save_name)) {
      unlink($save_name);
    }

    header('pragma:public');
    header('Content-type:application/vnd.ms-excel;charset=utf-8;name="' . $fileName . '.xls"');
    header("Content-Disposition:attachment;filename=$fileName.xls"); //attachment新窗口打印inline本窗口打印
    $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
    $objWriter->save($save_name);
    return $save_name;
  }

  function import_to_array($filename) {

    $exts = pathinfo($filename, PATHINFO_EXTENSION);

    vendor("PHPExcel.PHPExcel");
    //创建PHPExcel对象，注意，不能少了\
    $PHPExcel = new \PHPExcel();
    //如果excel文件后缀名为.xls，导入这个类
    if ($exts == 'xls') {
      vendor("PHPExcel.PHPExcel.Reader.Excel5");

      $PHPReader = new \PHPExcel_Reader_Excel5();
    } else if ($exts == 'xlsx') {
      vendor("PHPExcel.PHPExcel.Reader.Excel2007");
      $PHPReader = new \PHPExcel_Reader_Excel2007();
    }

    //载入文件
    $PHPExcel = $PHPReader->load($filename);
    //获取表中的第一个工作表，如果要获取第二个，把0改为1，依次类推
    $currentSheet = $PHPExcel->getSheet(0);
    //获取总列数
    $allColumn = $currentSheet->getHighestColumn();
    //获取总行数
    $allRow = $currentSheet->getHighestRow();
    $data = array();
    //循环获取表中的数据，$currentRow表示当前行，从哪行开始读取数据，索引值从0开始
    for ($currentRow = 2; $currentRow <= $allRow; $currentRow++) {
      //从哪列开始，A表示第一列
      for ($currentColumn = 'A'; $currentColumn <= $allColumn; $currentColumn++) {
        //数据坐标
        $address = $currentColumn . $currentRow;
        //读取到的数据，保存到数组$arr中
        $data[$currentRow][$currentColumn] = $currentSheet->getCell($address)->getValue();
      }
    }

    return $data;
  }

}
